-- exec sp_products_list_by_type
ALTER        proc sp_products_list_by_type

as
begin
	declare @results Table(ProcessStep_ID int, IST_Manufacturing_Part_Number varchar (50), IST_Marketing_Part_Number varchar (50), Product_ID int, P_Type varchar (50), P_Comments varchar (200) )

	insert into @results select Process_Step.ProcessStep_ID, Product.Part_Number, Product.P_Marketing_part_Number, Product.Product_ID, Product.P_Type, Product.P_Comments from product,Process_Step  where product.P_Type is null and product.is_approved=0 and product.ProcessStep_ID in (select processstep_id from Process_Step where PS_Type_Name like 'Finished Goods') and product.processStep_id=Process_Step.ProcessStep_ID order by Product.Part_Number

	insert into @results values(null,'---- BOARD -----','---- BOARD -----',null,null,null)

	insert into @results select Process_Step.ProcessStep_ID, Product.Part_Number, Product.P_Marketing_part_Number, Product.Product_ID, Product.P_Type,Product.P_Comments from product,Process_Step  where product.P_Type = 'BOARD' and product.is_approved=0 and product.ProcessStep_ID in (select processstep_id from Process_Step where PS_Type_Name like 'Finished Goods') and product.processStep_id=Process_Step.ProcessStep_ID order by Product.Part_Number

	insert into @results values(null,'---- KIT -----','---- KIT -----',null,null,null)

	insert into @results select Process_Step.ProcessStep_ID, Product.Part_Number, Product.P_Marketing_part_Number, Product.Product_ID, Product.P_Type,Product.P_Comments from product,Process_Step  where product.P_Type = 'KIT' and product.is_approved=0 and product.ProcessStep_ID in (select processstep_id from Process_Step where PS_Type_Name like 'Finished Goods') and product.processStep_id=Process_Step.ProcessStep_ID order by Product.Part_Number
end

select * from @results   

